package com.example.demo5.dao;

import com.example.demo4.Bean.News;
import com.example.demo4.Bean.Notice;
import com.example.demo4.utils.DBConnection;
import com.example.demo4.utils.Time;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class NoticeDao {
    DBConnection DBCoon = new DBConnection();
    //todo 完成增删改查功能
    public List<Notice> getList(int page) {
        Connection conn = DBCoon.getConn();
        String sql = "select * from notice limit "+(page-1)*10+",10";
        List<Notice> list = new ArrayList<Notice>();
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                Notice noticeBean = new Notice();
                noticeBean.setNoticeid(rs.getInt("noticeid"));
                noticeBean.setNoticetitle(rs.getString("noticetitle"));
                noticeBean.setNoticecontent(rs.getString("noticecontent"));
                noticeBean.setNoticetime(rs.getString("noticetime"));
                noticeBean.setAdminname(rs.getString("aminname"));
                list.add(noticeBean);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    public int updateNotice(Notice notice) {
        if (notice.getNoticeid() > 0) {
            Connection conn = DBCoon.getConn();
            try {
                String sql = "update notice set noticetitle=?,noticecontent=?,noticetime=?,aminname=? where noticeid=?";
                PreparedStatement pmst = conn.prepareStatement(sql);
                pmst.setString(1, notice.getNoticetitle());
                pmst.setString(2, notice.getNoticecontent());
                pmst.setString(4, Time.getDateTime());
                pmst.setString(3, notice.getAdminname());
                pmst.setInt(5, notice.getNoticeid());
                return pmst.executeUpdate();
            } catch (SQLException e){
                e.printStackTrace();
            }
            return 0;
        }
        return 0;
    }

    public int add(Notice notice){
        Connection conn = DBCoon.getConn();
        try {
            String sql = "insert into notice values(null,?,?,?,?)";
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setString(1,notice.getNoticetitle());
            pmst.setString(2,notice.getNoticecontent());
            pmst.setString(3, (Time.getDateTime()));
            pmst.setString(4,"临时管理员");
            System.out.println(pmst);
            return pmst.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return 0;
    }
    public int deleteNoticeById(int newsid){
        Connection conn = DBCoon.getConn();
        try {
            String sql = "delete from notice where noticeid=?";
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setInt(1,newsid);
            return pmst.executeUpdate();
        }catch (SQLException e){
            System.out.println("deleteNewsById error");
            e.printStackTrace();
        }
        return 0;
    }

    public int countPage() {
        Connection conn = DBCoon.getConn();
        String sql = "select count(*) from notice";
        try {
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);//获取查询结果
            if (rs.next()){
                return (rs.getInt(1)/10)+1;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return 0;
    }

    public Notice getNoticeByID(int noticeID) {
        Connection conn = DBCoon.getConn();
        Notice noticeBean = new Notice();
        try {
            String sql = "select * from notice where noticeid=?";
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setInt(1, noticeID);
            ResultSet rs = pmst.executeQuery();
            if (rs.next()){
                noticeBean.setNoticeid(rs.getInt(1));
                noticeBean.setNoticetitle(rs.getString(2));
                noticeBean.setNoticecontent(rs.getString(3));
                noticeBean.setNoticetime(rs.getString(4));
                noticeBean.setAdminname(rs.getString(5));
                return noticeBean;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return null;
    }
}

